package com.zg.autoform.web;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zg.autoform.enm.ColumnCompEnum;
import com.zg.autoform.enm.LayoutTypeEnum;
import com.zg.autoform.web.req.BatchUpdateColumnReq;
import com.zg.common.core.C;
import com.zg.common.core.R;
import com.zg.common.core.User;
import com.zg.common.core.dao.autoform.*;
import com.zg.common.core.util.IdUtil;
import com.zg.common.core.util.JsonUtil;
import com.zg.common.dds.DbTypeUtil;
import com.zg.common.web.Req;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.sql.Timestamp;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Zxb
 */
@RestController
@RequiredArgsConstructor
@Api(tags = "表单数据管理")
public class ZxbDataController {
    private final DiyFormDao formDao;
    private final DiyViewDao viewDao;
    private final DiyColumnDao columnDao;
    private final DiyLayoutDao layoutDao;
    private final DiyConditionDao conditionDao;
    private final NamedParameterJdbcTemplate jdbcTemplate;
    private final DbTypeUtil dbTypeUtil;

    @ApiOperation(value = "分页查询表单数据")
    @PostMapping("/zxb/table/data/page/{formCode}")
    public R<?> page(@PathVariable("formCode") String formCode,
                     @RequestBody Map<String, Object> map) {
        //获取表单
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda()
                .eq(DiyForm::getCode, formCode));
        if (form == null) {
            return R.error("表单不存在");
        }
        //修正page和pageSize参数
        int page = (int) map.getOrDefault("page", 1);
        int pageSize = (int) map.getOrDefault("pageSize", 10);

        //获取过滤器配置
        List<DiyCondition> conditionList = conditionDao.list(
                new QueryWrapper<DiyCondition>()
                        .lambda()
                        .eq(DiyCondition::getFormId, form.getId())
        );

        //过滤器
        StringBuffer conditionSb = new StringBuffer("1=1");
        Map<String, Object> conditionMap = new LinkedHashMap<>();
        if (CollUtil.isNotEmpty(map) && CollUtil.isNotEmpty(conditionList)) {
            conditionList.stream().forEach(t -> {
                String code = t.getColumnCode();
                String alias = t.getAlias();
                String type = t.getColumnType();
                String val = map.get(alias) == null ? "" : String.valueOf(map.get(alias));
                if (StrUtil.isNotBlank(val)) {
                    if ("like".equals(t.getType())) {
                        conditionSb.append(" and (" + code + " like :" + code + ")");
                        conditionMap.put(code, "%" + val + "%");
                    } else {
                        conditionSb.append(" and (" + code + " " + t.getType() + " :" + code + ")");
                        if (ColumnCompEnum.整数.name().equals(type)) {
                            conditionMap.put(code, Integer.valueOf(val));
                        } else if (ColumnCompEnum.浮点数.name().equals(type)) {
                            conditionMap.put(code, Double.valueOf(val));
                        } else if (ColumnCompEnum.日期.name().equals(type) || ColumnCompEnum.日期时间.name().equals(type)) {
                            conditionMap.put(code, DateUtil.parse(val).toJdkDate());
                        } else if ((ColumnCompEnum.单选框.name().equals(type)
                                || ColumnCompEnum.开关.name().equals(type))) {
                            if (dbTypeUtil.isOracle()) {
                                conditionMap.put(code, val.equals("true") ? 1 : 0);
                            } else {
                                conditionMap.put(code, val.equals("true"));
                            }
                        } else {
                            conditionMap.put(code, val);
                        }
                    }
                }
            });
        }

        //获取总数
        String totalSql = " SELECT count(*) FROM " + formCode + " WHERE " + conditionSb;
        Integer total = jdbcTemplate.queryForObject(totalSql, conditionMap, Integer.class);

        String sql = " SELECT * FROM " + formCode + " WHERE " + conditionSb;
        List<Map<String, Object>> list = null;
        if (pageSize >= total) {
            //总数小于一页
            list = jdbcTemplate.queryForList(sql, conditionMap);
            handleLongTypeProperty(list);
            return R.success(Arrays.asList(total, list));
        }

        // 封装分页sql
        String pageSql = "";
        // 第一页查询，直接用rowNum即可（oracle写法）
        if (dbTypeUtil.isOracle()) {
            if (page == 1) {
                pageSql = "SELECT * FROM (" + sql + ") t WHERE rownum <= :rowNum";
                conditionMap.put("rowNum", pageSize);
            } else {
                pageSql = "SELECT * FROM ( SELECT t.*, ROWNUM rn FROM(" + sql + ") t WHERE ROWNUM <= :endRow ) WHERE rn > :startRow";
                conditionMap.put("startRow", (page - 1) * pageSize);
                conditionMap.put("endRow", page * pageSize);
            }
        }
        if (dbTypeUtil.isMysql()) {
            pageSql = "select * from (" + sql + ") t limit :startRow, :pageSize";
            conditionMap.put("startRow", (page - 1) * pageSize);
            conditionMap.put("pageSize", pageSize);
        }
        list = jdbcTemplate.queryForList(pageSql, conditionMap);
        handleLongTypeProperty(list);
        return R.success(Arrays.asList(total, list));
    }

    @ApiOperation(value = "通过id查询表单信息")
    @PostMapping("/zxb/table/data/infoById/{formCode}")
    public R<?> infoById(@PathVariable("formCode") String formCode,
                         @RequestBody Map<String, Object> map) {
        //获取表单
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda()
                .eq(DiyForm::getCode, formCode));
        if (form == null) {
            return R.error("表单不存在");
        }
        String id = map.get("id") + "";
        if (ObjectUtil.isEmpty(id)) {
            return R.error("id不存在");
        }
        //过滤器
        StringBuffer conditionSb = new StringBuffer("id =" + id);
        Map<String, Object> conditionMap = new LinkedHashMap<>();

        String sql = " SELECT * FROM " + formCode + " WHERE " + conditionSb;
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, conditionMap);

        handleLongTypeProperty(list);
        Map<String, Object> resultMap = new HashMap<>();
        if (CollectionUtil.isNotEmpty(list)) {
            resultMap = list.get(0);
        }
        return R.success(resultMap);
    }

    /**
     * 处理Long类型字段，转成字符串再传给前端，避免丢失精度
     */
    private void handleLongTypeProperty(List<Map<String, Object>> list) {
        String KEY = "ID";
        list.stream().forEach(m -> {
            m.put(KEY, String.valueOf(m.get(KEY)));
        });
    }

    @ApiOperation(value = "新增表单数据")
    @PostMapping("/zxb/table/data/add/{formCode}")
    public R add(@PathVariable("formCode") String formCode,
                 @RequestBody Map<String, Object> map) {
        //查询表单
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, formCode));
        //新增时默认字段
        List<String> pubColumnList = Arrays.asList(C.DEFAULT_COLUMNS);
        //查询新增视图
        List<DiyView> view = viewDao.list(
                new LambdaQueryWrapper<DiyView>()
                        .eq(DiyView::getFormId, form.getId())
                        .eq(DiyView::getType, "add")
        );
        List<DiyLayout> layoutList = layoutDao.list(
                new LambdaQueryWrapper<DiyLayout>()
                        .eq(DiyLayout::getViewId, view.get(0).getId())
        );

        //合并新增视图中的字段和默认字段
        List<String> allColumnList = new ArrayList<>();
        allColumnList.addAll(pubColumnList);
        allColumnList.addAll(layoutList.stream().map(t -> t.getColumnCode()).collect(Collectors.toList()));

        //生成sql插入字段部分
        String fieldNames = allColumnList.stream()
                .collect(Collectors.joining(","));
        //生成sql值部分
        String values = allColumnList.stream()
                .map(t -> ":" + t)
                .collect(Collectors.joining(","));

        String tableName = formCode;
        //完整sql
        String sql = "INSERT INTO " + tableName + "(" + fieldNames + ") VALUES (" + values + ")";
        Long id = IdUtil.nextId();
        //参数中追加公共字段的值
        map.put("id", id);
        //获取当前用户
        User user = Req.getCurrUser();
        String username = user != null ? user.getUsername() : null;
        map.put("create_by", username);
        map.put("update_by", username);
        map.put("ver", 0);
        map.put("is_del", dbTypeUtil.isOracle() ? 0 : true);
        //设置模糊匹配字段
        handleSearchStrProperty(form.getId(), map);
        Timestamp now = new Timestamp(System.currentTimeMillis());
        map.put("create_time", now);
        map.put("update_time", now);
        //处理日期格式
        handleDateProperty(layoutList, map);
        //执行入库操作
        jdbcTemplate.update(sql, map);
        //统计数据量
        countData(form);
        return R.success(id);
    }

    /**
     * 拼接模糊匹配字符串
     */
    private void handleSearchStrProperty(Long formId, Map<String, Object> map) {
        StringBuffer searchStrSb = new StringBuffer("");
        List<DiyColumn> likeColumnList = columnDao.list(new QueryWrapper<DiyColumn>()
                .lambda()
                .eq(DiyColumn::getFormId, formId)
                .eq(DiyColumn::getIsLike, true));
        if (CollUtil.isNotEmpty(likeColumnList)) {
            likeColumnList.stream().forEach(t -> {
                searchStrSb.append(map.get(t.getCode().toLowerCase()));
            });
        }
        map.put("search_str", searchStrSb.toString());
    }

    /**
     * 处理日期格式字段，将字符串转化为java.sql.Date
     */
    private void handleDateProperty(List<DiyLayout> list, Map<String, Object> map) {
        list.stream()
                .forEach(t -> {
                    String code = t.getColumnCode();
                    if (ColumnCompEnum.日期.name().equals(t.getColumnType())) {
                        String strVal = (String) map.get(code);
                        java.sql.Date sqlDate = DateUtil.parse(strVal).toSqlDate();
                        map.put(code, sqlDate);
                    } else if (ColumnCompEnum.日期时间.name().equals(t.getColumnType())) {
                        String strVal = (String) map.get(code);
                        Timestamp sqlTimestamp = DateUtil.parse(strVal).toTimestamp();
                        map.put(code, sqlTimestamp);
                    } else if (ColumnCompEnum.单选框.name().equals(t.getColumnType()) || ColumnCompEnum.开关.name().equals(t.getColumnType())) {
                        if (dbTypeUtil.isOracle()) {
                            if (map.get(code) instanceof Boolean) {
                                Boolean boolVal = (Boolean) map.get(code);
                                map.put(code, boolVal ? 1 : 0);
                            }
                        }
                    }
                });
    }

    /**
     * 从视图中获取字段
     */
    private List<String> generateColumnList(List<DiyLayout> layoutList) {
        List<String> columnList = layoutList.stream()
                .map(t -> t.getColumnCode()).collect(Collectors.toList());
        return columnList;
    }

    @ApiOperation(value = "更新表单数据")
    @PostMapping("/zxb/table/data/update/{formCode}")
    public R update(@PathVariable("formCode") String formCode,
                    @RequestBody Map<String, Object> map) {
        //查询表单
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, formCode));
        //更新时的默认字段
        List<String> pubColumnList = Arrays.asList("update_by", "update_time", "search_str");
        //获取更新视图
        List<DiyView> view = viewDao.list(
                new LambdaQueryWrapper<DiyView>()
                        .eq(DiyView::getFormId, form.getId())
                        .eq(DiyView::getType, "update")
        );
        List<DiyLayout> layoutList = layoutDao.list(
                new LambdaQueryWrapper<DiyLayout>()
                        .eq(DiyLayout::getViewId, view.get(0).getId())
        );

        //合并更新视图中的字段和默认字段
        List<String> allColumnList = new ArrayList<>();
        allColumnList.addAll(pubColumnList);
        allColumnList.addAll(layoutList.stream().map(t -> t.getColumnCode()).collect(Collectors.toList()));

        //生成sql中赋值部分
        String fieldNames = allColumnList
                .stream()
                .map(f -> f + "=:" + f)
                .collect(Collectors.joining(","));

        //完整sql，特殊处理版本号，乐观锁机制
        String sql = " UPDATE " + formCode + " SET " + fieldNames + ",ver=:newVer WHERE id=:id AND ver=:oldVer ";
        //获取当前用户
        User user = Req.getCurrUser();
        String username = user != null ? user.getUsername() : null;
        map.put("update_by", username);
        Timestamp now = new Timestamp(System.currentTimeMillis());
        map.put("update_time", now);
        //设置模糊匹配字段
        handleSearchStrProperty(form.getId(), map);
        //乐观锁参数
        int oldVer = (int) map.getOrDefault("ver", 1);
        map.put("oldVer", oldVer);
        map.put("newVer", oldVer + 1);
        //处理日期格式
        handleDateProperty(layoutList, map);
        //执行更新操作
        int result = jdbcTemplate.update(sql, map);
        if (result == 0) {
            return R.error("更新失败，请稍后重试");
        }
        return R.success();
    }

    @ApiOperation(value = "批量更新表单数据")
    @PostMapping("/zxb/table/data/batch/{tableCode}")
    public R batch(@PathVariable("tableCode") String tableCode,
                   @RequestBody Map<String, Object> map) {
        //获取表单
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, tableCode));
        //获取批量IDS
        List<Long> idList = (List<Long>) map.get("ids");
        if (CollUtil.isEmpty(idList)) {
            return R.error("缺少ids参数");
        }

        //批量更新时的默认字段
        List<String> allColumnList = new ArrayList<>();
        List<String> pubColumnList = Arrays.asList("update_by", "update_time");
        allColumnList.addAll(pubColumnList);

        //获取批量更新视图
        List<DiyLayout> layoutList = layoutDao.list(new QueryWrapper<DiyLayout>()
                .lambda()
                .eq(DiyLayout::getFormId, form.getId())
                .eq(DiyLayout::getType, LayoutTypeEnum.batch.name()));
        //从批量更新视图中获取批量更新字段
        List<String> columnList = generateColumnList(layoutList);
        //追加用户要更新的字段
        for (String str : columnList) {
            boolean hasColumn = (boolean) map.getOrDefault(str + "_ck", false);
            if (hasColumn) {
                allColumnList.add(str);
            }
        }

        //生成sql赋值部分
        String fieldNames = allColumnList
                .stream()
                .map(f -> f + "=:" + f.toLowerCase())
                .collect(Collectors.joining(","));
        String sql = " UPDATE " + tableCode + " SET " + fieldNames + " WHERE id in (:ids) ";

        //获取当前用户
        User user = Req.getCurrUser();
        String username = user != null ? user.getUsername() : null;
        map.put("update_by", username);
        Timestamp now = new Timestamp(System.currentTimeMillis());
        map.put("update_time", now);

        //执行批量更新操作
        int result = jdbcTemplate.update(sql, map);
        if (result == 0) {
            return R.error("更新失败，请稍后重试");
        } else {
            return R.success(result);
        }
    }

    @ApiOperation(value = "批量更新表单单个字段")
    @PostMapping("/zxb/table/data/batchForColumn")
    public R batchForColumn(@RequestBody BatchUpdateColumnReq req) {

        if (CollUtil.isEmpty(req.getIds())) {
            return R.error("缺少ids参数");
        }

        //批量更新时的默认字段
        List<String> allColumnList = new ArrayList<>();
        List<String> pubColumnList = Arrays.asList("update_by", "update_time");
        allColumnList.addAll(pubColumnList);
        allColumnList.add(req.getCode());
        Map<String, Object> map = new HashMap<>();
        String compType = req.getCompType();
        map.put("ids", req.getIds());
        map.put(req.getCode(), req.getStrVal());
        if ("单选框".equals(compType) || "开关".equals(compType)) {
            if (dbTypeUtil.isOracle()) {
                map.put(req.getCode(), "true".equals(req.getBoolVal()) ? 1 : 0);
            } else {
                map.put(req.getCode(), "true".equals(req.getBoolVal()));
            }
        } else if ("整数".equals(compType)) {
            map.put(req.getCode(), Integer.valueOf(req.getNumVal()));
        } else if ("浮点数".equals(compType)) {
            map.put(req.getCode(), Double.valueOf(req.getNumVal()));
        }
        //生成sql赋值部分
        String fieldNames = allColumnList
                .stream()
                .map(f -> f + "=:" + f)
                .collect(Collectors.joining(","));
        String sql = " UPDATE " + req.getFormCode() + " SET " + fieldNames + " WHERE id in (:ids) ";

        //获取当前用户
        User user = Req.getCurrUser();
        String username = user != null ? user.getUsername() : null;
        map.put("update_by", username);
        Timestamp now = new Timestamp(System.currentTimeMillis());
        map.put("update_time", now);

        //执行批量更新操作
        int result = jdbcTemplate.update(sql, map);
        if (result == 0) {
            return R.error("更新失败，请稍后重试");
        } else {
            return R.success(result);
        }
    }

    @ApiOperation(value = "批量删除表单数据")
    @PostMapping("/zxb/table/data/del/{formCode}")
    public R del(@PathVariable("formCode") String formCode,
                 @RequestBody List<Long> ids) {
        String sql = " DELETE FROM " + formCode + " WHERE id in (:ids) ";
        Map<String, Object> param = new HashMap<>(1);
        param.put("ids", ids);
        jdbcTemplate.update(sql, param);

        //统计数据量
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, formCode));
        if (form != null) {
            countData(form);
        }
        return R.success();
    }

    /**
     * 统计数据量
     */
    private void countData(DiyForm form) {
        String countSql = " select count(*) from " + form.getCode() + " where is_del = :isDel ";
        Map<String, Object> countMap = new HashMap<>(1);
        countMap.put("isDel", 0);
        int count = jdbcTemplate.queryForObject(countSql, countMap, Integer.class);
        form.setDataCount(count);
        formDao.updateById(form);
    }

    @ApiOperation(value = "统计表单数据量")
    @GetMapping("/zxb/table/data/count")
    public R countData(String tableCode) {
        if (StrUtil.isBlank(tableCode)) {
            return R.success(0);
        }
        try {
            String sql = " select count(*) from " + tableCode + " where is_del = :isDel ";
            Map<String, Object> map = new HashMap<>(1);
            map.put("isDel", 0);
            int count = jdbcTemplate.queryForObject(sql, map, Integer.class);
            return R.success(count);
        } catch (Exception e) {
            return R.success(0);
        }
    }

    @ApiOperation(value = "模拟表单数据校验")
    @PostMapping("/zxb/table/data/valid/{state}")
    public R validSuccess(@PathVariable("state") String state,
                          @RequestBody Map<String, Object> map) {
        String OK = "ok";
        try {
            String param = JsonUtil.OM.writeValueAsString(map);
            System.out.println("==============>接收到参数：");
            System.out.println(param);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (OK.equals(state)) {
            return R.success();
        } else {
            return R.error("远程数据校验失败");
        }
    }
}
